import logging
import os
from flask import Blueprint, jsonify, request
from app.utils.spi_utils import read_spi_csv_file
from app.config.paths import SMT2_SPI_PATH

smt2_spi_bp = Blueprint('smt2_spi', __name__)

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# 定义SMT2 SPI数据文件的Linux路径
# SMT2_SPI_PATH = "/mnt/smt2/spi/"


def get_smt_npm_scan_record(mysql_tool, serial_number):
    """
    查询锡膏信息（根据SFCNo动态确定表名）
    """

    logger.info(f"流水号码：{serial_number}")

    # 使用参数化查询防止SQL注入
    smt_sql = """
       SELECT
  smt_npm_scan_record.batch_code,
  smt_npm_scan_record.serial_number
FROM
  smt_npm_scan_record
WHERE
  smt_npm_scan_record.batch_code = (SELECT a.batch_code FROM smt_npm_scan_record AS a WHERE a.serial_number = %s LIMIT 1)
    """

    logger.info(f"执行的SQL语句：{smt_sql}, 参数：{serial_number}")
    results = mysql_tool.execute_query(smt_sql, (serial_number,))
    smt_data = []
    for row in results:
        item = {
            "batchCode": row[0],
            "serialNumber": row[1],
        }
        smt_data.append(item)
    return smt_data

@smt2_spi_bp.route('/api/smt2spi/query/<serial_number>', methods=['GET'])
def query_smt2_spi_data(serial_number):
    """SMT2 SPI数据查询接口，通过流水号查询对应的SPI测试数据"""
    try:
        if not serial_number:
            return jsonify({"success": False, "message": "流水号码不能为空"}), 400

        logger.info(f"查询SMT2 SPI数据，流水号: {serial_number}")

        # 获取MySQL工具实例
        from mysql_tool.mysql_tool import MySQLTool
        mysql_tool = MySQLTool()

        # 获取同一批次的所有流水号
        serial_numbers = get_smt_npm_scan_record(mysql_tool, serial_number)

        if not serial_numbers:
            logger.warning(f"未找到流水号 {serial_number} 的批次信息")
            # 如果找不到批次信息，也尝试使用原始流水号查询
            serial_numbers = [{"serialNumber": serial_number}]
        else:
            logger.info(f"找到同批次流水号 {len(serial_numbers)} 个")

        # 遍历所有可能的流水号
        for sn_record in serial_numbers:
            sn = sn_record["serialNumber"]
            logger.info(f"尝试使用流水号 {sn} 查找SPI文件")

            # 构建文件路径
            file_path = os.path.join(SMT2_SPI_PATH, f"{sn}.csv")
            logger.info(f"尝试从SMT2路径读取SPI数据: {file_path}")

            # 检查文件是否存在
            if os.path.exists(file_path):
                try:
                    # 读取并解析CSV文件
                    spi_data = read_spi_csv_file(file_path)

                    # 返回解析后的数据
                    return jsonify({
                        "success": True,
                        "data": spi_data,
                        "file_path": file_path,
                        "used_serial_number": sn,  # 添加实际使用的流水号
                        "original_serial_number": serial_number  # 添加原始查询流水号
                    })

                except Exception as e:
                    logger.error(f"读取SMT2 SPI数据文件失败: {str(e)}")
                    continue

        # 所有流水号都尝试失败
        return jsonify({
            "success": False,
            "message": f"无法找到流水号 {serial_number} 及其关联流水号对应的SMT2 SPI数据文件"
        }), 404

    except Exception as e:
        logger.error(f"查询SMT2 SPI信息失败: {str(e)}")
        return jsonify({
            "success": False,
            "message": f"查询失败: {str(e)}"
        }), 500


@smt2_spi_bp.route('/api/smt2spi/latest', methods=['GET'])
def query_latest_smt2_spi_data():
    """获取SMT2 SPI最新的一条数据记录"""
    try:
        logger.info("查询SMT2 SPI最新数据")

        # 检查目录是否存在
        if not os.path.exists(SMT2_SPI_PATH):
            return jsonify({
                "success": False,
                "message": f"SMT2 SPI数据目录不存在: {SMT2_SPI_PATH}"
            }), 404

        # 获取目录中所有CSV文件
        csv_files = [os.path.join(SMT2_SPI_PATH, f) for f in os.listdir(SMT2_SPI_PATH)
                     if f.endswith('.csv') and os.path.isfile(os.path.join(SMT2_SPI_PATH, f))]

        if not csv_files:
            return jsonify({
                "success": False,
                "message": "未找到任何SPI数据文件"
            }), 404

        # 按照文件修改时间排序，获取最新的文件
        latest_file = max(csv_files, key=os.path.getmtime)

        # 从文件名中提取流水号
        file_name = os.path.basename(latest_file)
        serial_number = os.path.splitext(file_name)[0]  # 移除.csv扩展名

        logger.info(f"找到最新SPI数据文件: {latest_file}")

        try:
            # 读取并解析CSV文件
            spi_data = read_spi_csv_file(latest_file)

            # 返回解析后的数据
            return jsonify({
                "success": True,
                "data": spi_data,
                "file_path": latest_file,
                "serial_number": serial_number
            })

        except Exception as e:
            logger.error(f"读取最新SMT2 SPI数据文件失败: {str(e)}")
            return jsonify({
                "success": False,
                "message": f"读取最新SPI数据文件失败: {str(e)}"
            }), 500

    except Exception as e:
        logger.error(f"查询最新SMT2 SPI信息失败: {str(e)}")
        return jsonify({
            "success": False,
            "message": f"查询失败: {str(e)}"
        }), 500
